﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.SECURITY.MODEL;

namespace KPIS.GERP.SECURITY.DAL
{
    public class UserDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public UserDAL()
        {

        }
        public DataSet LoadUser(Nullable<int> Sequence, Nullable<int> EmployeeType, Nullable<int> RoleSequence, string user_code)
        {
            string strSQL;
            DataSet ds;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@EmployeeType", EmployeeType);
                dbManager.AddParameters(2, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@user_code", user_code);

                strSQL = "SELECT u.*"
                    + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS USER_NAME"
                    + ", p.POS_REF, p.POS_NAME, j.JOB_SITE_NAME, w.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME"
                    + ", c.CLASS_NAME"
                    + " FROM SYS_SC_USERS u"
                    //+ " LEFT JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                    //+ " LEFT JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = ur.ROLE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES w ON e.WORKING_JOB_SITE_SEQ = w.JOB_SITE_SEQ"
                    + " WHERE u.RECORD_STATUS = @RecordStatus"
                    + (EmployeeType.HasValue ? " AND e.EMP_TYPE_SEQ = @EmployeeType" : "")
                    + (user_code.Length > 0 ? " AND u.user_code = @user_code" : "")
                    //+ (RoleSequence.HasValue ? " AND ur.ROLE_SEQ = @RoleSequence" : "")
                    
                    + (Sequence.HasValue ? " AND u.USER_SEQ = @Sequence" : "");
                
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public DataSet LoadUserRole(Nullable<int> Sequence, Nullable<int> EmployeeType, Nullable<int> RoleSequence, string user_code)
        {
            string strSQL;
            DataSet ds;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@EmployeeType", EmployeeType);
                dbManager.AddParameters(2, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@user_code", user_code);

                strSQL = "SELECT u.*"
                    + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS USER_NAME"
                    + ", p.POS_REF, p.POS_NAME, j.JOB_SITE_NAME, w.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME"
                    + ", c.CLASS_NAME"
                    + " FROM SYS_SC_USERS u"
                    + " LEFT JOIN SYS_SC_USER_ROLES ur ON u.USER_SEQ = ur.USER_SEQ"
                    + " LEFT JOIN SYS_SC_ROLES r ON ur.ROLE_SEQ = ur.ROLE_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES w ON e.WORKING_JOB_SITE_SEQ = w.JOB_SITE_SEQ"
                    + " WHERE u.RECORD_STATUS = @RecordStatus"
                    + (EmployeeType.HasValue ? " AND e.EMP_TYPE_SEQ = @EmployeeType" : "")
                    + (user_code.Length > 0 ? " AND u.user_code = @user_code" : "")
                    + (RoleSequence.HasValue ? " AND ur.ROLE_SEQ = @RoleSequence" : "")

                    + (Sequence.HasValue ? " AND u.USER_SEQ = @Sequence" : "");
                strSQL += @" group by u.user_seq,
       u.emp_seq, 
       u.user_job_site_seq, 
       u.user_pos_seq, 
       u.user_code, 
       u.user_password, 
       u.is_admin, 
       u.user_citizen, 
       u.user_status, 
       u.record_status, 
       u.created_by, 
       u.created_when, 
       u.updated_by, 
       u.updated_when, 
       u.user_first_name, 
       u.user_last_name,  
	   np.PREFIX_NAME, e.FIRST_NAME, e.LAST_NAME,
	   p.POS_REF, 
p.POS_NAME, 
j.JOB_SITE_NAME,
w.JOB_SITE_NAME, 
c.CLASS_NAME ";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public bool CheckPassword(string UserName, string Password)
        {
            string strSQL;
            bool bOK = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@UserName", UserName);
                dbManager.AddParameters(1, "@Password", Password);

                DataSet ds = null;
                DataRowCollection drc;

                strSQL = "SELECT *"
                    + " FROM SYS_SC_USERS"
                    + " WHERE USER_CODE = @UserName"
                        + " AND USER_PASSWORD = @Password";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables.Count > 0)
                {
                    drc = ds.Tables[0].Rows;
                    bOK = drc.Count > 0;
                }
                return bOK;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }
        public void Insert(Nullable<int> EmpSequence, string UserID, string Password, string UserStatus, int CreatedBy)
        {
            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(1, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(2, "@UserID", UserID);
                dbManager.AddParameters(3, "@Password", Password);
                dbManager.AddParameters(4, "@UserStatus", UserStatus);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO SYS_SC_USERS"
                    + "(EMP_SEQ, USER_CODE, USER_PASSWORD, USER_STATUS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@EmpSequence, @UserID, @Password, @UserStatus, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int Sequence, Nullable<int> EmpSequence, string UserID, string Password, string UserStatus
            , int UpdatedBy)
        {          

            try
            {
                string strSQL;
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@Sequene", Sequence);
                dbManager.AddParameters(1, "@EmpSequence", EmpSequence);
                dbManager.AddParameters(2, "@UserID", UserID);
                dbManager.AddParameters(3, "@Password", Password);
                dbManager.AddParameters(4, "@UserStatus", UserStatus);
                dbManager.AddParameters(5, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(6, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_USERS"
                   + " SET EMP_SEQ = @EmpSequence"
                   + ", USER_CODE = @UserID"
                   + ", USER_PASSWORD = @Password"
                   + ", USER_STATUS = @UserStatus"
                   + ", UPDATED_BY = @UpdatedBy"
                   + ", UPDATED_WHEN = @UpdatedWhen"
                   + " WHERE USER_SEQ = @Sequene";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_USERS"
                    + " SET RECORD_STATUS = @DeleteRecord"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE USER_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
       
    }
}
